﻿/*
#**************************************************************************
#  openSIS is a student information system for public and non-public 
#  schools from Open Solutions for Education, Inc. web: www.os4ed.com
#
#  openSIS is  web-based, open source, and comes packed with features that 
#  include student demographic info, scheduling, grade book, attendance, 
#  report cards, eligibility, transcripts, parent portal, 
#  student portal and more.   
#
#  Visit the openSIS web site at http://www.opensis.com to learn more.
#  If you have question regarding this system or the license, please send 
#  an email to info@os4ed.com.
#
#  This program is released under the terms of the GNU General Public License  
#  as  published by the Free Software Foundation, version 2 of the License. 
#  See license.txt.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
#**************************************************************************** 

*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using OpenSISDataConnection;
using OSISStudentInfoBusinessLayer;

namespace OSISStudentInfoDataLayer
{
    public class StudentInfoDL
    {
        public StudentInfoDL()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        public static string server = System.Configuration.ConfigurationManager.AppSettings["appserver"];

        public System.Data.DataTable StudentListBySchoolID(StudentInfoBL studentinfo)
        {
            DataTable dt = null;
            //dt = DataConnection.ConnectionPath.Getdata("SELECT [std_id],(SELECT CAST(value + '  ' AS VARCHAR(MAX)) FROM [student_details] WHERE (student_details.std_id = student_school.std_id and student_school.sch_id=" + studentinfo.SchoolID + " and student_details.field_id in (5,7) and student_details.field_category='D') FOR XML PATH ('')) AS student_details FROM [student_school] where student_school.sch_id=" + studentinfo.SchoolID + "").Tables[0];
            dt = DataConnection.ConnectionPath.Getdata("select dFirstName.std_id,dFirstName.value as firstname,dLastName.value as lastname,dGrade.value as grade,dEnrollmentDate.value as enrollmentdate,dStdSchId.value as StdSchId FROM student_school s left outer join student_details dFirstName on s.std_id=dFirstName.std_id and dFirstName.field_id=5 left outer join student_details dLastName on s.std_id=dLastName.std_id and dLastName.field_id=7 left outer join student_details dGender on s.std_id=dGender.std_id and dGender.field_id=10 left outer join student_details dEthnicity on s.std_id=dEthnicity.std_id and dEthnicity.field_id=11 left outer join student_details dDOB on s.std_id=dDOB.std_id and dDOB.field_id=13 left outer join student_details dGrade on s.std_id=dGrade.std_id and dGrade.field_id=109 left outer join student_details dEnrollmentDate on s.std_id=dEnrollmentDate.std_id and dEnrollmentDate.field_id=107 left outer join student_details dStdSchId on s.std_id=dStdSchId.std_id and dStdSchId.field_id=1 and dStdSchId.field_category='D' WHERE s.sch_id=" + studentinfo.SchoolID + " and dFirstName.value like '" + studentinfo.FName + "%' and dLastName.value like '" + studentinfo.LName + "%' and dGender.value like '" + studentinfo.Gender + "%' and dEthnicity.value like '" + studentinfo.Ethnicity + "%' and dDOB.value like '" + studentinfo.DOB + "%' and dEnrollmentDate.value like '" + studentinfo.EnrollmentDate + "%' and dStdSchId.value like '" + studentinfo.StdSchId + "%'").Tables[0];



            return dt;

        }

        public System.Data.DataTable GetStudentDetailsForFirstCategory(StudentInfoBL studentinfo)
        {
            DataTable dt = null;
            //dt = DataConnection.ConnectionPath.Getdata("SELECT [std_id],(SELECT CAST(value + '  ' AS VARCHAR(MAX)) FROM [student_details] WHERE (student_details.std_id = student_school.std_id and student_school.sch_id=" + studentinfo.SchoolID + " and student_details.field_id in (5,7) and student_details.field_category='D') FOR XML PATH ('')) AS student_details FROM [student_school] where student_school.sch_id=" + studentinfo.SchoolID + "").Tables[0];
            dt = DataConnection.ConnectionPath.Getdata("select SSFO.Sort_Order,SD.std_id,SD.field_category,SD.field_id,SD.value,SD.cat_id from Student_Setup_FormLayout_Order SSFO inner join student_details SD on SSFO.Field_Id=SD.field_id where SD.std_id=" + studentinfo.StudentID + " and SSFO.Sch_Id=(select Sch_Id from student_school where std_id=" + studentinfo.StudentID + ") and SD.cat_id=1 order by SSFO.Sort_Order").Tables[0];



            return dt;

        }

        public System.Data.DataTable GetFieldNameByFieldId(StudentInfoBL studentinfo)
        {
            DataTable dt = null;

            dt = DataConnection.ConnectionPath.Getdata("select field_name from Student_Setup_Default_Field where Def_Field_Id=" + studentinfo.FieldId + "").Tables[0];



            return dt;

        }

        //public System.Data.DataTable GetStudentsBySearch(StudentInfoBL studentinfo)
        //{
        //    DataTable dt = null;
        //    //dt = DataConnection.ConnectionPath.Getdata("select SD.std_id,SD.value from student_details SD inner join student_school SS on SD.std_id=SS.std_id where SS.sch_id=" + studentinfo.SchoolID + " and SD.field_id in (5,7) and SD.field_category='D' and (SD.value like '" + studentinfo.FName + "%' or SD.value LIKE '" + studentinfo.LName + "%')").Tables[0];

        //    dt = DataConnection.ConnectionPath.Getdata("SELECT [std_id], (SELECT CAST(value + '  ' AS VARCHAR(MAX)) FROM [student_details] WHERE (student_details.std_id = student_school.std_id and student_school.sch_id=" + studentinfo.SchoolID + " and student_details.field_id in (5,7) and student_details.field_category='D' and (student_details.value like '%" + studentinfo.FName + "%' or student_details.value like '%" + studentinfo.LName + "%') ) FOR XML PATH ('')) AS student_details FROM [student_school] where student_school.sch_id=" + studentinfo.SchoolID + "").Tables[0];

        //    return dt;

        //}

        public System.Data.DataTable GetFieldValueByCategory(StudentInfoBL FieldValue)
        {
            DataTable dtdefault = null;
            DataTable dtcustom = null;
            DataTable dt = null;

            dt = DataConnection.ConnectionPath.Getdata("select * from Student_Setup_ViewFormLayout_Order where Sch_Id=" + FieldValue.SchoolID + " and Cat_Id=" + FieldValue.CatId + "").Tables[0];
            if (dt.Rows.Count == 0)
            {


                dtdefault = DataConnection.ConnectionPath.Getdata("select distinct SD.std_id,SD.field_category,(case when SD.field_category ='D' then (select field_name from Student_Setup_Default_Field SSDF where SSDF.Def_Field_Id=SD.field_id) else (select field_name from Student_Setup_custom_Field SSCF where SSCF.extrafield_id=SD.field_id ) end) as FieldName,SD.value,SD.cat_id,SD.field_id from  student_details SD  left outer join (select * from Student_Setup_Default_Field_Status where School_Id =" + FieldValue.SchoolID + " and Status='A' and (Field_View='V_E') and (Status!='I' or Status IS NULL))as SSDFS on SD.field_id=SSDFS.Default_Field_Id where SD.std_id=" + FieldValue.StudentID + " AND SD.cat_id=" + FieldValue.CatId + " AND SD.field_category ='D' and SSDFS.Status='A' and SSDFS.Field_View='V_E' and (SSDFS.Status!='I' or SSDFS.Status IS NULL)").Tables[0];
                dtcustom = DataConnection.ConnectionPath.Getdata("select distinct SD.std_id,SD.field_category,(case when SD.field_category ='C' then (select DISTINCT field_name from Student_Setup_custom_Field SSCF where SSCF.extrafield_id=SD.field_id) end) as FieldName,SD.value,SD.cat_id,SD.field_id from student_details SD   left outer join (select * from Student_Setup_custom_Field where sch_id =" + FieldValue.SchoolID + " and Status='A' and (Field_View='V_E') and (Status!='I' or Status IS NULL)) as SSCF on SD.field_id=SSCF.extrafield_id where SD.std_id=" + FieldValue.StudentID + " AND SD.cat_id=" + FieldValue.CatId + " and  SD.field_category ='C'").Tables[0];

                dtdefault.Merge(dtcustom);
                DataView myDataView = dtdefault.DefaultView;


                return myDataView.ToTable();

            }
            else
            {
                dtdefault = DataConnection.ConnectionPath.Getdata("select distinct SSFO.Sort_Order,SD.std_id,SD.field_category,(case when SD.field_category ='D' then (select field_name from Student_Setup_Default_Field SSDF where SSDF.Def_Field_Id=SD.field_id) else (select field_name from Student_Setup_custom_Field SSCF where SSCF.extrafield_id=SD.field_id ) end) as FieldName,SD.value,SD.cat_id,SD.field_id from Student_Setup_ViewFormLayout_Order SSFO left outer join student_details SD on SSFO.Field_Id=SD.field_id left outer join (select * from Student_Setup_Default_Field_Status where School_Id =" + FieldValue.SchoolID + " and Status='A' and (Field_View='V_E') and (Status!='I' or Status IS NULL))as SSDFS on SD.field_id=SSDFS.Default_Field_Id where SD.std_id=" + FieldValue.StudentID + " AND SD.cat_id=" + FieldValue.CatId + " and SSFO.Sch_Id=(select Sch_Id from student_school where std_id=" + FieldValue.StudentID + ")  AND SD.field_category ='D' AND SSFO.FL_FieldType='D' and SSDFS.Status='A' and SSDFS.Field_View='V_E' and (SSDFS.Status!='I' or SSDFS.Status IS NULL) order by SSFO.Sort_Order").Tables[0];

                dtcustom = DataConnection.ConnectionPath.Getdata("select distinct SSFO.Sort_Order,SD.std_id,SD.field_category,(case when SD.field_category ='C' then (select DISTINCT field_name from Student_Setup_custom_Field SSCF where SSCF.extrafield_id=SD.field_id) end) as FieldName,SD.value,SD.cat_id,SD.field_id from student_details SD  left outer join  Student_Setup_ViewFormLayout_Order SSFO on SSFO.Field_Id=SD.field_id left outer join (select * from Student_Setup_custom_Field where sch_id =" + FieldValue.SchoolID + " and Status='A' and (Field_View='V_E') and (Status!='I' or Status IS NULL)) as SSCF on SD.field_id=SSCF.extrafield_id where SD.std_id=" + FieldValue.StudentID + " AND SD.cat_id=" + FieldValue.CatId + " and SSFO.Sch_Id=(select Sch_Id from student_school where std_id=" + FieldValue.StudentID + ") AND SD.field_category ='C' and SSFO.FL_FieldType='C' order by SSFO.Sort_Order").Tables[0];
                dtdefault.Merge(dtcustom);
                DataView myDataView = dtdefault.DefaultView;
                myDataView.Sort = "Sort_Order";

                return myDataView.ToTable();
                //return dtdefault;


            }
        }

        public System.Data.DataTable GetAddressFieldValues(StudentInfoBL FieldValue)
        {
            DataTable dtdefault = null;
            //DataTable dtcustom = null;

            //dtdefault = DataConnection.ConnectionPath.Getdata("select SSAF.Field_Name,SAD.add_id,SAD.add_value,SAD.def_field_id,SAD.cat_id from Student_Adddress_Details SAD inner join Student_Setup_Address_Field SSAF on SAD.add_id=SSAF.Add_Id left outer join (select * from Student_Setup_Address_Default_Field_Status where Sch_Id =" + FieldValue.SchoolID + " and Status='A' and (Field_View='V_E') and (Status!='I' or Status IS NULL)) AS SSADFS on SAD.add_id=SSADFS.Add_Id where SAD.std_id=" + FieldValue.StudentID + " and SAD.cat_id=" + FieldValue.CatId + "  and SAD.def_field_id=" + FieldValue.FieldId + " and SSADFS.Field_View='V_E'").Tables[0];
            //dtcustom = DataConnection.ConnectionPath.Getdata("select distinct SD.std_id,SD.field_category,(case when SD.field_category ='C' then (select DISTINCT field_name from Student_Setup_custom_Field SSCF where SSCF.extrafield_id=SD.field_id) end) as FieldName,SD.value,SD.cat_id,SD.field_id from student_details SD   left outer join (select * from Student_Setup_custom_Field where sch_id =" + FieldValue.SchoolID + " and Status='A' and (Field_View='V' or Field_View='V_E') and (Status!='I' or Status IS NULL)) as SSCF on SD.field_id=SSCF.extrafield_id where SD.std_id=" + FieldValue.StudentID + " AND SD.cat_id=" + FieldValue.CatId + " and  SD.field_category ='C'").Tables[0];
            dtdefault = DataConnection.ConnectionPath.Getdata("select SSAF.Add_Id as FieldId,SSAF.Field_name as FieldName,SSAF.Field_Type as FieldType,SSADFS.Status as Status,SSAF.Status as IsMandatory,SSDF.DefaultField as Default_Custom,SSADFS.Field_View from Student_Setup_Address_Field SSAF left outer join(select * from Student_Setup_Address_Default_Field_Status  where Sch_Id =" + FieldValue.SchoolID + " and Status='A')as SSADFS on SSAF.Add_Id=SSADFS.Add_Id left outer join(select * from Student_Setup_Default_Field where Def_Field_Id=" + FieldValue.FieldId + ") SSDF on SSAF.Def_Field_Id=SSDF.Def_Field_Id WHERE SSAF.Def_Field_Id=" + FieldValue.FieldId + " and (SSADFS.Field_View='V_E') order by SSAF.Add_Id").Tables[0];

            //dtdefault.Merge(dtcustom);
            //DataView myDataView = dtdefault.DefaultView;


            //return myDataView.ToTable();
            return dtdefault;


        }

        public System.Data.DataTable GetDefaultAddressFields(StudentInfoBL AddressFields)
        {
            DataTable dtdefault = null;


            //dtdefault = DataConnection.ConnectionPath.Getdata("select  SSDF.field_name as FieldName from Student_Adddress_Details SAD inner join Student_Setup_Default_Field SSDF on SAD.def_field_id=SSDF.Def_Field_Id where sch_id=" + AddressFields.SchoolID + " and std_id=" + AddressFields.StudentID + " group by SAD.def_field_id,SSDF.field_name").Tables[0];
            dtdefault = DataConnection.ConnectionPath.Getdata("select distinct SSDF.field_name as FieldName,SAD.def_field_id as field_id,SAD.cat_id from Student_Adddress_Details SAD inner join Student_Setup_Default_Field SSDF on SAD.def_field_id=SSDF.Def_Field_Id where sch_id=" + AddressFields.SchoolID + " and std_id=" + AddressFields.StudentID + " group by SAD.def_field_id,SSDF.field_name,SAD.cat_id").Tables[0];


            return dtdefault;


        }

        public System.Data.DataTable getsummaryfields(StudentInfoBL SummaryFields)
        {

            DataTable dtcustom = null;



            // dtcustom = DataConnection.ConnectionPath.Getdata("select Def_Field_Id as FieldId,(case when Def_Field_Id =13 then 'DOB | Age' else field_name end) as FieldName,Field_Type as FieldType,Status,Cat_Id from Student_Setup_Default_Field where Def_Field_Id in (1,13,110,11,15) order by case Def_Field_Id when 1 then 1 when 13 then 2  when 110 then 3 when 11 then 4 when 15 then 5 end").Tables[0];

            dtcustom = DataConnection.ConnectionPath.Getdata("select SSDF.Def_Field_Id,(case when SSDF.Def_Field_Id =13 then 'DOB | Age' else field_name end) as field_name,SD.value from (select distinct * from Student_Setup_Default_Field where Def_Field_Id in(1,13,110,11,15)) SSDF left outer join (select * from student_details where std_id=" + SummaryFields.StudentID + ") SD on SSDF.Def_Field_Id=SD.field_id order by case SSDF.Def_Field_Id when 1 then 1 when 13 then 2  when 110 then 3 when 11 then 4 when 15 then 5 end").Tables[0];

            return dtcustom;

        }

        public System.Data.DataTable getsummaryheaderfields(StudentInfoBL SummaryFields)
        {

            DataTable dtcustom = null;



            // dtcustom = DataConnection.ConnectionPath.Getdata("select Def_Field_Id as FieldId,(case when Def_Field_Id =13 then 'DOB | Age' else field_name end) as FieldName,Field_Type as FieldType,Status,Cat_Id from Student_Setup_Default_Field where Def_Field_Id in (1,13,110,11,15) order by case Def_Field_Id when 1 then 1 when 13 then 2  when 110 then 3 when 11 then 4 when 15 then 5 end").Tables[0];

            dtcustom = DataConnection.ConnectionPath.Getdata("select SSDF.Def_Field_Id,SSDF.field_name,SD.value from (select distinct * from Student_Setup_Default_Field where Def_Field_Id in(5,6,7,109,174)) SSDF left outer join (select * from student_details where std_id=" + SummaryFields.StudentID + ") SD on SSDF.Def_Field_Id=SD.field_id").Tables[0];

            return dtcustom;

        }

        public System.Data.DataTable GetNextStudentId(StudentInfoBL studentinfo)
        {
            if (server == "MSSQLServer")
            {

                DataTable dt = null;
                dt = DataConnection.ConnectionPath.Getdata("select top 1 std_id  from student_school where sch_id=" + studentinfo.SchoolID + " and std_id>" + studentinfo.StudentID + "").Tables[0];
                return dt;
            }
            if (server == "MySQL")
            {
                DataTable dt = null;
                dt = DataConnection.ConnectionPath.Getdata("select  std_id  from student_school where sch_id=" + studentinfo.SchoolID + " and std_id>" + studentinfo.StudentID + " limit 1 ").Tables[0];
                return dt;
            }
            else
            {
                DataTable dt = null;

                return dt;
            }
        }

        public System.Data.DataTable GetPreviousStudentId(StudentInfoBL studentinfo)
        {
            if (server == "MSSQLServer")
            {
                DataTable dt = null;
                dt = DataConnection.ConnectionPath.Getdata("select top 1 std_id  from student_school where sch_id=" + studentinfo.SchoolID + " and std_id<" + studentinfo.StudentID + " order by std_id desc").Tables[0];
                return dt;
            }
            if (server == "MySQL")
            {
                DataTable dt = null;
                dt = DataConnection.ConnectionPath.Getdata("select std_id from student_school where sch_id=" + studentinfo.SchoolID + " and std_id<" + studentinfo.StudentID + " order by std_id desc limit 1").Tables[0];
                return dt;
            }
            else
            {
                DataTable dt = null;

                return dt;
            }
        }

        public System.Data.DataTable GetFirstStudentIdBySchoolId(StudentInfoBL studentinfo)
        {
            if (server == "MSSQLServer")
            {
                DataTable dt = null;
                dt = DataConnection.ConnectionPath.Getdata("select top 1 std_id  from student_school where sch_id=" + studentinfo.SchoolID + "").Tables[0];
                return dt;
            }
            if (server == "MySQL")
            {
                DataTable dt = null;
                dt = DataConnection.ConnectionPath.Getdata("select std_id  from student_school where sch_id=" + studentinfo.SchoolID + " limit 1").Tables[0];
                return dt;
            }
            else
            {
                DataTable dt = null;

                return dt;
            }
        }

        public System.Data.DataTable GetLastStudentIdBySchoolId(StudentInfoBL studentinfo)
        {
            if (server == "MSSQLServer")
            {
                DataTable dt = null;
                dt = DataConnection.ConnectionPath.Getdata("select top 1 std_id  from student_school where sch_id=" + studentinfo.SchoolID + " order by std_id desc").Tables[0];
                return dt;
            }
            if (server == "MySQL")
            {
                DataTable dt = null;
                dt = DataConnection.ConnectionPath.Getdata("select std_id  from student_school where sch_id=" + studentinfo.SchoolID + " order by std_id desc limit 1").Tables[0];
                return dt;
            }
            else
            {
                DataTable dt = null;

                return dt;
            }
        }

        public System.Data.DataTable GetEthnicity(StudentInfoBL studentinfo)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select eth_nm from ethnicity where eth_id=" + studentinfo.EthnicityId + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable GetGender(StudentInfoBL studentinfo)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select gender_nm from gender where gender_id=" + studentinfo.Gender + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable GetLanguage(StudentInfoBL studentinfo)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select language_nm from language where language_id=" + studentinfo.Language + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable GetCountryName(StudentInfoBL studentinfo)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("SELECT * FROM look_up_data WHERE TYPE=1 and IID=" + studentinfo.CountryCode + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable GetCalenderName(StudentInfoBL studentinfo)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select * from Calender_Details where Caleder_Id=" + studentinfo.Calender + " and Sch_Id=" + studentinfo.SchoolID + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable GetCustomListItemName(StudentInfoBL studentinfo)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select * from Custom_Field_List_Item WHERE ListItem_Id=" + studentinfo.ListItemId + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable GetControlType(StudentInfoBL studentcontrols)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select * from Student_Setup_Custom_Field where extrafield_id=" + studentcontrols.FieldId + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable GetStartCondition(StudentInfoBL startcdns)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select * from enrollment_codes where enr_cd_id=" + startcdns.StartCondition + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable GetPhotoURL(StudentInfoBL studentphoto)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select * from student_picture WHERE std_id=" + studentphoto.StudentID + " and Sch_id=" + studentphoto.SchoolID + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable GetStateName(StudentInfoBL studentstate)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("SELECT * FROM look_up_data WHERE TYPE=2 and IID=" + studentstate.State + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable GetContactRelationshipName(StudentInfoBL studentinfo)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select * from person_relationship where person_rel_id=" + studentinfo.PersonRelId + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable GetAdditionalFieldValues(StudentInfoBL studentinfo)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select Value,Cont_Id as FieldId,'C' FieldCatagory, Contact_Set,'' def_field_id from Student_Additional_Contact where Std_Id=" + studentinfo.StudentID + " and Cat_Id=" + studentinfo.CatId + "   and sch_Id=" + studentinfo.SchoolID + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable populateadditionalfield(StudentInfoBL AddressFields)
        {

            DataTable dtadditional = null;
            DataTable dtHomeAddress = null;

            dtadditional = DataConnection.ConnectionPath.Getdata("select SSADCF.Cont_Id as FieldId,SSADCF.Field_Name as FieldName,SSADCF.Field_Type as FieldType,SSACS.Status as Status,SSADCF.Status  as IsMandatory,'C' as Default_Custom,SSACS.Field_View from Student_Setup_Additional_Contact_Status SSACS inner join Student_Setup_Additional_Default_Contact_Field SSADCF on SSACS.Cont_Id =SSADCF.Cont_Id  where Sch_Id=" + AddressFields.SchoolID + " and SSACS.Status='A' and (Field_View='V_E' or Field_View='E') order by SSADCF.Cont_Id").Tables[0];


            dtHomeAddress = DataConnection.ConnectionPath.Getdata("select SSAF.Add_Id as FieldId,SSAF.Field_name as FieldName,SSAF.Field_Type as FieldType,SSADFS.Status as Status,SSAF.Status as IsMandatory,'C' as Default_Custom,SSADFS.Field_View from Student_Setup_Address_Field SSAF left outer join(select * from Student_Setup_Address_Default_Field_Status  where Sch_Id =" + AddressFields.SchoolID + " and Status='A')as SSADFS on SSAF.Add_Id=SSADFS.Add_Id left outer join(select * from Student_Setup_Default_Field where Def_Field_Id=" + AddressFields.HomeFieldId + ") SSDF on SSAF.Def_Field_Id=SSDF.Def_Field_Id WHERE SSAF.Def_Field_Id=" + AddressFields.HomeFieldId + " and (SSADFS.Field_View='V_E' or SSADFS.Field_View='E') order by SSAF.Add_Id").Tables[0];

            dtadditional.Merge(dtHomeAddress);


            return dtadditional;

        }






        public System.Data.DataTable populateaddressfields(StudentInfoBL AddressFields)
        {

            DataTable dtadditional = null;
            DataTable dtdefault = null;


            dtdefault = DataConnection.ConnectionPath.Getdata("select add_value as value,SAD.add_id as FieldId,'D' FieldCatagory,'0' Contact_set,cast(def_field_id as varchar(max)) from Student_Adddress_Details SAD left outer join (select * from Student_Setup_Address_Default_Field_Status  where Sch_Id =" + AddressFields.SchoolID + " and Status='A') as SSADFS on SSADFS.Add_Id=SAD.add_id where SAD.std_id=" + AddressFields.StudentID + " and SAD.cat_id=" + AddressFields.CatId + " and SAD.sch_id=" + AddressFields.SchoolID + " and SSADFS.Field_View='V_E'").Tables[0];

            dtadditional = DataConnection.ConnectionPath.Getdata("select Value,Cont_Id as FieldId,'C' FieldCatagory,Contact_Set,'' def_field_id from Student_Additional_Contact where Std_Id=" + AddressFields.StudentID + " and Cat_Id=" + AddressFields.CatId + " and sch_Id=" + AddressFields.SchoolID + "").Tables[0];

            dtdefault.Merge(dtadditional);

            return dtdefault;




        }


        //---------------------------------------------------------------------------------------------------
        public System.Data.DataTable getmedicalinfofieldvalue(StudentInfoBL studentinfo)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select SMID.Med_Info_Id as FieldId,SSMI.Field_Name as FieldName,SMID.Value from Student_Medical_Information_Details SMID inner join Student_Setup_Medical_Information SSMI on SMID.Med_Info_Id =SSMI.Med_Info_Id where SMID.Std_Id =" + studentinfo.StudentID + " and SMID.Sch_Id =" + studentinfo.SchoolID + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable getimmunizationfieldvalue(StudentInfoBL studentinfo)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select * from Student_Immunization_Details where std_id =" + studentinfo.StudentID + " and sch_id =" + studentinfo.SchoolID + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable getmedicalalertfieldvalue(StudentInfoBL studentinfo)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select stu_med_alert_id,stu_med_alert_nm as Alert from student_medical_alert where stu_id=" + studentinfo.StudentID + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable getnursevisitfieldvalue(StudentInfoBL studentinfo)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select Visit_Date as Date,Time_In as TimeIn,Time_Out as TimeOut,Reason as Reason,Result as Result,Comment as Comments from Student_Nurse_Visit_Record_Details where Std_Id=" + studentinfo.StudentID + " and Sch_Id=" + studentinfo.SchoolID + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable getvaccinationfieldvalue(StudentInfoBL studentinfo)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select * from Student_Vaccination_Record_Details where Std_Id=" + studentinfo.StudentID + " and Sch_Id =" + studentinfo.SchoolID + "").Tables[0];
            return dt;
        }
        public DataTable Getstudentschoolenroll(StudentInfoBL studentinfo)
        {

            DataTable dt = null;



            String strStudentEnrollment = "select student_enrollment.*,ENR.enr_cd_nm as enrollname  from student_enrollment inner join enrollment_codes ENR on student_enrollment.Enr_Code_Id = ENR.enr_cd_id where Std_Id = " + studentinfo.StudentID + " and Sch_Id= " + studentinfo.SchoolID + "";

            dt = DataConnection.ConnectionPath.Getdata(strStudentEnrollment).Tables[0];

            return dt;


        }
        public string GetdynamicallycreatedDataItems(StudentInfoBL studentinfo)
        {

            string s = "";


            String strStudentEnrollment = " select ListItem_name as ListName from Custom_Field_List_Item where control_id=" + studentinfo.ExtrafieldId + " and ListItem_Id = " + studentinfo.ListItemId + "";

            s = DataConnection.ConnectionPath.Getdata(strStudentEnrollment).Tables[0].Rows[0][0].ToString();

            return s;


        }

        //Special and gifted starts here

        public DataTable GetSpecialValues(StudentInfoBL studentinfo)
        {

            DataTable dt = null;



            //String strStudentEnrollment = "select  SSPSDED.std_id,SSPSDED.value,SSPSDED.cat_id,SSPSDED.Spl_Id as FieldId,SSPDSE.Field_Name as FieldName from  Student_SetUp_Programs_Special_Education_Details SSPSDED  left outer join (select * from Student_SetUp_Programs_Default_Special_Education_Status where sch_id =" + studentinfo.SchoolID + " and Status='A' and  (Spl_Field_View='V_E' or Spl_Field_View='V') and (Status!='I' or Status IS NULL))as SSPDSES on SSPSDED.Spl_Id=SSPDSES.Spl_Id  left outer join Student_SetUp_Programs_Default_Special_Education as SSPDSE on SSPSDED.Spl_Id = SSPDSE.Spl_Id where SSPSDED.std_id=" + studentinfo.StudentID + " AND SSPSDED.cat_id= " + studentinfo.CatId + "    and SSPDSES.Status='A' and SSPDSES.Spl_Field_View='V_E'   and  (SSPDSES.Status!='I' or SSPDSES.Status IS NULL) order by Std_Prog_Spl_Id";

            String strStudentEnrollment = "  select SSPSED.Std_Prog_Spl_Id,SSPSED.Cat_Id,SSPDSE.Spl_Id as FieldId, SSPDSE.Field_Name as FieldName,SSPDSE.Field_Type as FieldType,SSPSED.Value from    Student_SetUp_Programs_Default_Special_Education as SSPDSE left outer join     (select Spl_Id,Spl_Field_View as Field_View,Status from   Student_SetUp_Programs_Default_Special_Education_Status where sch_id = " + studentinfo.SchoolID + " )SSPDSES on SSPDSE.Spl_Id = SSPDSES.Spl_Id  left outer join      Student_SetUp_Programs_Special_Education_Details SSPSED on SSPDSE.Spl_Id = SSPSED.Spl_Id where Std_Id = " + studentinfo.StudentID + " and Cat_Id= " + studentinfo.CatId + " and  SSPDSES.Field_View = 'V_E' or SSPDSES.Field_View = 'V' and SSPDSES.Status ='A'";


            dt = DataConnection.ConnectionPath.Getdata(strStudentEnrollment).Tables[0];

            return dt;


        }

        public DataTable GetGiftedValues(StudentInfoBL studentinfo)
        {

            DataTable dt = null;



            String strStudentEnrollment = "select SSPSED.Std_Prog_Gft_Id,SSPSED.Cat_Id,SSPDSE.Gft_Id as FieldId, SSPDSE.Field_Name as FieldName,SSPDSE.Field_Type as FieldType,SSPSED.Value as Value from Student_Setup_Programs_Gifted_Default_Field as SSPDSE left outer join (select Gft_Id,Gft_Field_View as Field_View,Status from    Student_Setup_Programs_Gifted_Default_Field_Status where sch_id = " + studentinfo.SchoolID + " )SSPDSES on SSPDSE.Gft_Id = SSPDSES.Gft_Id  left outer join Student_Setup_Programs_Gifted_Details SSPSED  on SSPDSE.Gft_Id = SSPSED.Gft_Id where Std_Id = " + studentinfo.StudentID + " and Cat_Id= " + studentinfo.CatId + " and SSPDSES.Field_View = 'V_E' or SSPDSES.Field_View = 'V' and SSPDSES.Status ='A'  ";

            dt = DataConnection.ConnectionPath.Getdata(strStudentEnrollment).Tables[0];

            return dt;


        }



    }
}
